﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace TZReport_Util.Util
{
    public sealed class SqlHelper
    {
        private SqlConnection conn;

        public SqlHelper(string connStr) : this(new SqlConnection(connStr))
        {
        }

        public SqlHelper(SqlConnection conn)
        {
            this.conn = conn;
        }

        /// <summary>
        /// 支持多表插入事务处理
        /// </summary>
        /// <param name="lists"></param>
        public bool InsBulkCopyMultiTable<T>(params List<T>[] lists)
        {
            bool flag = false;
            conn.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            SqlTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;

            try
            {
                foreach (var list in lists)
                {
                    InsBulkCopySuppoutTransaction(list, conn, tx);
                }
                tx.Commit();
                flag = !flag;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
                tx.Rollback();
            }
            finally
            {
                if (null != conn)
                    conn.Close();
            }

            return flag;
        }

        /// <summary>
        /// 单表插入
        /// </summary>
        /// <param name="list"></param>
        public bool InsBulkCopy<T>(List<T> list, ref string failmsg)
        {
            if (list == null || list.Count == 0)
                return true;

            bool flag = false;
            try
            {
                conn.Open();
                using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
                {
                    var obj = list[0];
                    Type type = obj.GetType();
                    string dbnmae = type.Name;
                    //指定目标数据库的表名
                    bcp.DestinationTableName = $"[{dbnmae}]";
                    bcp.BulkCopyTimeout = 6000;
                    PropertyInfo[] props = type.GetProperties();
                    foreach (PropertyInfo p in props)
                    {
                        bcp.ColumnMappings.Add(p.Name, p.Name);
                    }

                    DataTable dt = ObjectToTable(list);
                    bcp.WriteToServer(dt);
                    //list.Clear(); // 清空内存空间
                    flag = !flag;
                }
            }
            catch (Exception e)
            {
                failmsg = e.Message;
                Console.WriteLine(e);
            }
            finally
            {
                if (null != conn)
                    conn.Close();
            }

            return flag;
        }

        public DataTable GetData(string sql, CommandType commandType)
        {
            try
            {
                conn.Open();
                // 第一个sql语句，第二个连接字符串
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                {
                    DataTable dt = new DataTable();

                    // 兼容存储过程
                    adapter.SelectCommand.CommandType = commandType;
                    adapter.Fill(dt);
                    return dt;
                }
            }
            finally
            {
                if (null != conn)
                    conn.Close();
            }
        }

        public string GetSingleDataString(string sql, CommandType commandType)
        {
            string result = string.Empty;
            DataTable dt = GetData(sql, commandType);
            if (dt != null && dt.Rows.Count > 0)
                result = dt.Rows[0][0].ToString();
            return result;
        }

        public int GetSingleDataInt(string sql, CommandType commandType)
        {
            int num = 0;
            string result = string.Empty;
            DataTable dt = GetData(sql, commandType);
            if (dt != null && dt.Rows.Count > 0)
            {
                result = dt.Rows[0][0].ToString();
                int.TryParse(result, out num);
            }

            return num;
        }

        public List<string> GetListDataString(string sql, CommandType commandType)
        {
            List<string> list = new List<string>();
            DataTable dt = GetData(sql, commandType);
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string str = dt.Rows[i][0].ToString();
                    list.Add(str);
                }
            }

            return list;
        }

        public List<int> GetListDataInt(string sql, CommandType commandType)
        {
            List<int> list = new List<int>();
            DataTable dt = GetData(sql, commandType);
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string str = dt.Rows[i][0].ToString();
                    list.Add(int.Parse(str));
                }
            }

            return list;
        }

        public List<T> GetData<T>(string sql, CommandType commandType) where T : class, new()
        {
            DataTable dt = GetData(sql, commandType);

            var list = new List<T>();
            var plist = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (DataRow item in dt.Rows)
            {
                T s = Activator.CreateInstance<T>();
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    PropertyInfo info = plist.Find(p => p.Name == dt.Columns[i].ColumnName);
                    if (info != null)
                    {
                        try
                        {
                            if (!Convert.IsDBNull(item[i]))
                            {
                                object v = null;
                                if (info.PropertyType.ToString().Contains("System.Nullable"))
                                {
                                    v = Convert.ChangeType(item[i], Nullable.GetUnderlyingType(info.PropertyType));
                                }
                                else
                                {
                                    v = Convert.ChangeType(item[i], info.PropertyType);
                                }
                                info.SetValue(s, v, null);
                            }
                        }
                        catch (Exception ex)
                        {
                            throw new Exception("字段[" + info.Name + "]转换出错," + ex.Message);
                        }
                    }
                }
                list.Add(s);
            }
            return list;
        }

        public bool ExecNonQuery(string sql)
        {
            bool b = false;
            try
            {
                int i = ExecNonQuery(sql, null);
                b = true;
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }

            return b;

        }

        public int ExecNonQuery(string sql, params SqlParameter[] parameter)
        {
            try
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    if (parameter != null && parameter.Length > 0)
                    {
                        cmd.Parameters.AddRange(parameter);
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (null != conn)
                    conn.Close();
            }
        }

        public bool InsT<T>(T t) where T : class
        {
            string sql = GetInsTSql(t);
            return ExecNonQuery(sql);
        }

        public string GetInsTSql<T>(T t) where T : class
        {
            string fieldNames = string.Empty;
            string values = StringUtils.ConvertObjToString(t, out fieldNames);
            string sql = $"INSERT INTO {t.GetType().Name}({fieldNames}) VALUES({values})";
            return sql;
        }

        private void InsBulkCopySuppoutTransaction<T>(List<T> list, SqlConnection conn, SqlTransaction tx)
        {
            using (SqlBulkCopy bcp = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, tx))
            {
                Type type = list[0].GetType();
                string dbnmae = type.Name;
                //指定目标数据库的表名
                bcp.DestinationTableName = $"[{dbnmae}]";

                PropertyInfo[] props = type.GetProperties();
                foreach (PropertyInfo p in props)
                {
                    bcp.ColumnMappings.Add(p.Name, p.Name);
                }
                DataTable dt = new DataTable();
                dt = ObjectToTable(list);

                bcp.WriteToServer(dt);
                list.Clear();
                dt.Clear();
            }
        }

        private DataTable ObjectToTable(object obj)
        {
            try
            {
                Type t;
                if (obj.GetType().IsGenericType)
                {
                    t = obj.GetType().GetGenericTypeDefinition();
                }
                else
                {
                    t = obj.GetType();
                }
                if (t == typeof(List<>) ||
                    t == typeof(IEnumerable<>))
                {
                    DataTable dt = new DataTable();
                    IEnumerable<object> lstenum = obj as IEnumerable<object>;
                    if (lstenum.Count() > 0)
                    {
                        var ob1 = lstenum.GetEnumerator();
                        ob1.MoveNext();
                        foreach (var item in ob1.Current.GetType().GetProperties())
                        {
                            dt.Columns.Add(new DataColumn() { ColumnName = item.Name });
                        }
                        //数据
                        foreach (var item in lstenum)
                        {
                            DataRow row = dt.NewRow();
                            foreach (var sub in item.GetType().GetProperties())
                            {
                                row[sub.Name] = sub.GetValue(item, null);
                            }
                            dt.Rows.Add(row);
                        }
                        return dt;
                    }
                }
                else if (t == typeof(DataTable))
                {
                    return (DataTable)obj;
                }
                else   //(t==typeof(Object))
                {
                    DataTable dt = new DataTable();
                    foreach (var item in obj.GetType().GetProperties())
                    {
                        dt.Columns.Add(new DataColumn() { ColumnName = item.Name });
                    }
                    DataRow row = dt.NewRow();
                    foreach (var item in obj.GetType().GetProperties())
                    {
                        row[item.Name] = item.GetValue(obj, null);
                    }
                    dt.Rows.Add(row);
                    return dt;
                }

            }
            catch
            {
            }
            return null;
        }

        private DataTable ToDataTable<T>(List<T> items)
        {
            var tb = new DataTable(typeof(T).Name);

            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            foreach (PropertyInfo prop in props)
            {
                Type t = GetCoreType(prop.PropertyType);
                tb.Columns.Add(prop.Name, t);
            }

            foreach (T item in items)
            {
                var values = new object[props.Length];

                for (int i = 0; i < props.Length; i++)
                {
                    values[i] = props[i].GetValue(item, null);
                }

                tb.Rows.Add(values);
            }

            return tb;
        }

        private bool IsNullable(Type t)
        {
            return !t.IsValueType || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>));
        }

        private Type GetCoreType(Type t)
        {
            if (t != null && IsNullable(t))
            {
                if (!t.IsValueType)
                    return t;
                else
                    return Nullable.GetUnderlyingType(t);
            }
            else
            {
                return t;
            }
        }
    }
}
